﻿using Common;
using Dm.util;
using FCT551.Models;
using MySqlConnector;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FCT551.Helper
{
    /// <summary>
    /// 数据库操作帮助类
    /// </summary>
    public static class SqlSugarHelper
    {
        /// <summary>
        /// 产能读取
        /// </summary>
        public static List<SearchData> SqlRead(string TableName, int ChoosedateIndex)
        {
            lock (Global.SqlSugarLock)
            {
                List<SearchData> data = new List<SearchData>();
                try
                {
                    bool tableExists = Global._sqlSugarClient.DbMaintenance.IsAnyTable(TableName, false);

                    if (tableExists)
                    {
                        //如果表存在
                        //如果存在，查询
                        List<CapacityData> list = Global._sqlSugarClient.Queryable<CapacityData>().AS(TableName).ToList();
                        if (ChoosedateIndex == 0)
                        {
                            //白班
                            for (int i = 8; i < 20; i++)
                            {
                                SearchData searchData = new SearchData();
                                searchData.Time = list[i].Time;
                                searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                                searchData.OKNum = list[i].OKnums.ToString();
                                searchData.NGnum = list[i].NGnums.ToString();
                                data.Add(searchData);
                            }
                        }
                        else if (ChoosedateIndex == 1)
                        {
                            //夜班
                            for (int i = 20; i < list.Count; i++)
                            {
                                SearchData searchData = new SearchData();
                                searchData.Time = list[i].Time;
                                searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                                searchData.OKNum = list[i].OKnums.ToString();
                                searchData.NGnum = list[i].NGnums.ToString();
                                data.Add(searchData);
                            }

                            for (int i = 0; i < 8; i++)
                            {
                                SearchData searchData = new SearchData();
                                searchData.Time = list[i].Time;
                                searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                                searchData.OKNum = list[i].OKnums.ToString();
                                searchData.NGnum = list[i].NGnums.ToString();
                                data.Add(searchData);
                            }
                        }
                        else
                        {
                            //白夜班合计
                            for (int i = 0; i < list.Count; i++)
                            {
                                SearchData searchData = new SearchData();
                                searchData.Time = list[i].Time;
                                searchData.Total = (list[i].OKnums + list[i].NGnums).ToString();
                                searchData.OKNum = list[i].OKnums.ToString();
                                searchData.NGnum = list[i].NGnums.ToString();
                                data.Add(searchData);
                            }
                        }
                        //返回一个总合计
                        SearchData AllData = new SearchData();
                        AllData.Total = data.Sum(s => int.Parse(s.Total)).ToString();
                        AllData.OKNum = data.Sum(s => int.Parse(s.OKNum)).ToString();
                        AllData.NGnum = data.Sum(s => int.Parse(s.NGnum)).ToString();
                        AllData.Time = "当前班次总计";
                        data.Add(AllData);
                    }
                    else
                    {
                        //如果表不存在
                        //不存在则创建新表
                        Global._sqlSugarClient.CodeFirst.As<CapacityData>(TableName).InitTables<CapacityData>();
                        //初始化表的24h产能
                        Global._sqlSugarClient.Insertable(Global._CapacityDatas).AS(TableName).ExecuteCommand();
                        throw new Exception("数据表不存在，已经创建新表!");
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }

                return data;
            }
        }

        /// <summary>
        ///根据当前日期，实现产能写入
        /// </summary>
        public static void SqlWrite(int Num, bool isOk)
        {
            lock (Global.SqlSugarLock)
            {
                DateTime alltime = DateTime.Now;
                string hourime = alltime.Hour.ToString() + ":00";
                if (alltime.Hour < 8)
                {
                    //八点之前，昨天的夜班
                    alltime = alltime.AddDays(-1);
                }

                if (alltime.Hour < 10)
                {
                    hourime = hourime.Insert(0, "0");
                }
                string TableName = alltime.ToString("yyyy-MM-dd") + "capacity";
                try
                {
                    //先判断表存不存在
                    ////表名固定格式
                    bool tableExists = Global._sqlSugarClient.DbMaintenance.IsAnyTable(TableName, false);
                    if (tableExists)
                    {
                        if (isOk)
                        {
                            //ok数据
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    OKnums = u.OKnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == hourime)
                                .ExecuteCommand();
                        }
                        else
                        {
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    NGnums = u.NGnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == hourime)
                                .ExecuteCommand();
                        }
                    }
                    else
                    {
                        //不存在则创建新表
                        Global._sqlSugarClient.CodeFirst.As<CapacityData>(TableName).InitTables<CapacityData>();
                        //初始化表的24h产能
                        Global._sqlSugarClient.Insertable(Global._CapacityDatas).AS(TableName).ExecuteCommand();
                        if (isOk)
                        {
                            //ok数据

                            //转化当前时间
                            string time = alltime.Hour.ToString() + ":00";

                            //修改数据
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    OKnums = u.OKnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == time)
                                .ExecuteCommand();
                        }
                        else
                        {
                            //ng数据

                            //转化当前时间
                            string time = alltime.Hour.ToString() + ":00";

                            //修改数据
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    NGnums = u.NGnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == time)
                                .ExecuteCommand();
                        }
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 读取年度产能或者当月产能
        /// </summary>
        public static int SqlReadByYear(string TableName, bool isYear)
        {
            lock (Global.SqlSugarLock)
            {
                int data = 0;

                bool tableExists = Global._sqlSugarClient.DbMaintenance.IsAnyTable(TableName, false);

                if (tableExists)
                {
                    List<CapacityData> list = Global._sqlSugarClient.Queryable<CapacityData>().AS(TableName).ToList();
                    if (isYear)
                    {
                        //查询年产能
                        data = list.Sum(s => s.OKnums);
                    }
                    else
                    {
                        //查询月产能
                        var mydata = list.Where(s => s.Time == DateTime.Now.Month.ToString()).ToList();
                        data = mydata[0].OKnums;
                    }
                }
                else
                {
                    //如果表不存在
                    //不存在则创建新表
                    Global._sqlSugarClient.CodeFirst.As<CapacityData>(TableName).InitTables<CapacityData>();
                    //初始化表的24h产能
                    Global._sqlSugarClient.Insertable(Global._CapacityDatasByYear).AS(TableName).ExecuteCommand();
                    throw new Exception("数据表不存在，已经创建新表!");
                }

                return data;
            }
        }

        /// <summary>
        ///根据当前日期，实现产能写入总表的对应月份的数据
        /// </summary>
        public static void SqlWriteByYear(string TableName, int Num, bool isOk)
        {
            lock (Global.SqlSugarLock)
            {
                string minute = DateTime.Now.Month.ToString();

                try
                {
                    //先判断表存不存在
                    ////表名固定格式
                    bool tableExists = Global._sqlSugarClient.DbMaintenance.IsAnyTable(TableName, false);
                    if (tableExists)
                    {
                        if (isOk)
                        {
                            //ok数据
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    OKnums = u.OKnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == minute)
                                .ExecuteCommand();
                        }
                        else
                        {
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    NGnums = u.NGnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == minute)
                                .ExecuteCommand();
                        }
                    }
                    else
                    {
                        //不存在则创建新表
                        Global._sqlSugarClient.CodeFirst.As<CapacityData>(TableName).InitTables<CapacityData>();
                        //初始化表的24h产能
                        Global._sqlSugarClient.Insertable(Global._CapacityDatasByYear).AS(TableName).ExecuteCommand();
                        if (isOk)
                        {
                            //修改数据
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    OKnums = u.OKnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == minute)
                                .ExecuteCommand();
                        }
                        else
                        {
                            //ng数据

                            //转化当前时间

                            //修改数据
                            Global._sqlSugarClient.Updateable<CapacityData>()
                                .SetColumns(u => new CapacityData()
                                {
                                    NGnums = u.NGnums + Num
                                }).AS(TableName)
                                .Where(u => u.Time == minute)
                                .ExecuteCommand();
                        }
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
    }
}